﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class StrategyKpiBLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable Search(string bgm_strat_kpi_seq, string bgm_kpi_type_seq, string bgm_strat_list_seq, string bgm_strat_kpi_year)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_strat_kpi_seq", bgm_strat_kpi_seq);
                dbManager.AddParameters(2, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(3, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(4, "@bgm_strat_kpi_year", bgm_strat_kpi_year);

                string sql = "select kt.*, sk.*, sk.bgm_strat_kpi_seq as Sequence "
                           + ", sk.target_m1 + sk.target_m2 + sk.target_m3 AS target_q1 "
                           + ", sk.target_m4 + sk.target_m5 + sk.target_m6 AS target_q2 "
                           + ", sk.target_m7 + sk.target_m8 + sk.target_m9 AS target_q3 "
                           + ", sk.target_m10 + sk.target_m11 + sk.target_m12 AS target_q4 "
                           + ", sk.target_m1 + sk.target_m2 + sk.target_m3 "
                               + "+ sk.target_m4 + sk.target_m5 + sk.target_m6 "
                               + "+ sk.target_m7 + sk.target_m8 + sk.target_m9 "
                               + "+ sk.target_m10 + sk.target_m11 + sk.target_m12 AS target_year" 
                           + " from bgm_strategy_kpi sk"
                           + " inner join bgm_mas_kpi_type kt on sk.bgm_kpi_type_seq = kt.bgm_kpi_type_seq"
                           + " WHERE sk.RECORD_STATUS not like @RecordStatus"
                           + ((bgm_strat_kpi_seq.Length > 0) ? " AND sk.bgm_strat_kpi_seq = @bgm_strat_kpi_seq" : "")
                           + ((bgm_kpi_type_seq.Length > 0) ? " AND sk.bgm_kpi_type_seq = @bgm_kpi_type_seq" : "")
                           + ((bgm_strat_list_seq.Length > 0) ? " AND sk.bgm_strat_list_seq = @bgm_strat_list_seq" : "")
                           + ((bgm_strat_kpi_year.Length > 0) ? " AND sk.bgm_strat_kpi_year = @bgm_strat_kpi_year" : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //public DataTable GetDataProjectKPIBySequence(string bgm_strat_kpi_seq)
        //{
        //    try
        //    {
        //        //dbManager.CreateParameters(1);
        //        //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
        //        string sql = "select *, bgm_strat_kpi_name as bgm_prj_kpi_name, bgm_strat_kpi_descr as bgm_prj_kpi_descr"
        //            + ", bgm_strat_kpi_weight as bgm_prj_kpi_weight"
        //                   + " from bgm_stratuest_kpi"
        //                   + " where bgm_strat_kpi_seq =" + bgm_strat_kpi_seq;

        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        //        dbManager.Dispose();
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        public void InsertStrategyKPI(string bgm_strat_kpi_name, string bgm_strat_kpi_descr, string unit, string bgm_strat_kpi_weight, string bgm_strat_seq, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type
                , string target_m1, string target_m2, string target_m3, string target_m4, string target_m5, string target_m6, string target_m7, string target_m8, string target_m9, string target_m10, string target_m11, string target_m12
                , string mtef_1, string mtef_2, string mtef_3, string bgm_strat_list_seq, string bgm_strat_kpi_year, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(31);
                dbManager.AddParameters(0, "@bgm_strat_kpi_name", bgm_strat_kpi_name);
                dbManager.AddParameters(1, "@bgm_strat_kpi_descr", bgm_strat_kpi_descr);
                dbManager.AddParameters(2, "@unit", unit);
                dbManager.AddParameters(3, "@bgm_strat_kpi_weight", bgm_strat_kpi_weight);
                dbManager.AddParameters(4, "@bgm_strat_seq", bgm_strat_seq);

                dbManager.AddParameters(5, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(6, "@score_1", String.IsNullOrEmpty(score_1) ? "0" : score_1);
                dbManager.AddParameters(7, "@score_2", String.IsNullOrEmpty(score_2) ? "0" : score_2);
                dbManager.AddParameters(8, "@score_3", String.IsNullOrEmpty(score_3) ? "0" : score_3);
                dbManager.AddParameters(9, "@score_4", String.IsNullOrEmpty(score_4) ? "0" : score_4);
                dbManager.AddParameters(10, "@score_5", String.IsNullOrEmpty(score_5) ? "0" : score_5);

                dbManager.AddParameters(11, "@target_type", target_type);
                dbManager.AddParameters(12, "@target_m1", String.IsNullOrEmpty(target_m1) ? "0" : target_m1);
                dbManager.AddParameters(13, "@target_m2", String.IsNullOrEmpty(target_m2) ? "0" : target_m2);
                dbManager.AddParameters(14, "@target_m3", String.IsNullOrEmpty(target_m3) ? "0" : target_m3);
                dbManager.AddParameters(15, "@target_m4", String.IsNullOrEmpty(target_m4) ? "0" : target_m4);
                dbManager.AddParameters(16, "@target_m5", String.IsNullOrEmpty(target_m5) ? "0" : target_m5);
                dbManager.AddParameters(17, "@target_m6", String.IsNullOrEmpty(target_m6) ? "0" : target_m6);
                dbManager.AddParameters(18, "@target_m7", String.IsNullOrEmpty(target_m7) ? "0" : target_m7);
                dbManager.AddParameters(19, "@target_m8", String.IsNullOrEmpty(target_m8) ? "0" : target_m8);
                dbManager.AddParameters(20, "@target_m9", String.IsNullOrEmpty(target_m9) ? "0" : target_m9);
                dbManager.AddParameters(21, "@target_m10", String.IsNullOrEmpty(target_m10) ? "0" : target_m10);
                dbManager.AddParameters(22, "@target_m11", String.IsNullOrEmpty(target_m11) ? "0" : target_m11);
                dbManager.AddParameters(23, "@target_m12", String.IsNullOrEmpty(target_m12) ? "0" : target_m12);
                dbManager.AddParameters(24, "@mtef_1", String.IsNullOrEmpty(mtef_1) ? "0" : mtef_1);
                dbManager.AddParameters(25, "@mtef_2", String.IsNullOrEmpty(mtef_2) ? "0" : mtef_2);
                dbManager.AddParameters(26, "@mtef_3", String.IsNullOrEmpty(mtef_3) ? "0" : mtef_3);

                dbManager.AddParameters(27, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(28, "@bgm_strat_kpi_year", bgm_strat_kpi_year);

                dbManager.AddParameters(29, "@created_by", created_by);
                dbManager.AddParameters(30, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_strategy_kpi ("
                                             + "bgm_strat_kpi_name,"
                                             + "bgm_strat_kpi_descr,"
                                             + "unit,"
                                             + "bgm_strat_kpi_weight,"
                                             //+ "bgm_strat_seq,"
                                             + "bgm_kpi_type_seq,"
                                             + "score_1,"
                                             + "score_2,"
                                             + "score_3,"
                                             + "score_4,"
                                             + "score_5,"
                                             + "target_type,"
                                             + "target_m1,"
                                             + "target_m2,"
                                             + "target_m3,"
                                             + "target_m4,"
                                             + "target_m5,"
                                             + "target_m6,"
                                             + "target_m7,"
                                             + "target_m8,"
                                             + "target_m9,"
                                             + "target_m10,"
                                             + "target_m11,"
                                             + "target_m12,"
                                             + "mtef_1,"
                                             + "mtef_2,"
                                             + "mtef_3,"
                                             + "bgm_strat_list_seq,"
                                             + "bgm_strat_kpi_year,"
                                             + "created_by,"
                                             + "created_when)"
                                             + " values("
                                             + "@bgm_strat_kpi_name,"
                                             + "@bgm_strat_kpi_descr,"
                                             + "@unit,"
                                             + "@bgm_strat_kpi_weight,"
                                             ////+ "@bgm_strat_seq,"
                                             + "@bgm_kpi_type_seq,"
                                             + "@score_1,"
                                             + "@score_2,"
                                             + "@score_3,"
                                             + "@score_4,"
                                             + "@score_5,"
                                             + "@target_type,"
                                             + "@target_m1,"
                                             + "@target_m2,"
                                             + "@target_m3,"
                                             + "@target_m4,"
                                             + "@target_m5,"
                                             + "@target_m6,"
                                             + "@target_m7,"
                                             + "@target_m8,"
                                             + "@target_m9,"
                                             + "@target_m10,"
                                             + "@target_m11,"
                                             + "@target_m12,"
                                             + "@mtef_1,"
                                             + "@mtef_2,"
                                             + "@mtef_3,"
                                             + "@bgm_strat_list_seq,"
                                             + "@bgm_strat_kpi_year,"
                                             + "@created_by,"
                                             + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateStrategyKPI(string Sequence, string bgm_strat_kpi_name, string bgm_strat_kpi_descr, string unit, string bgm_strat_kpi_weight, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type
            , string target_m1, string target_m2, string target_m3, string target_m4, string target_m5, string target_m6, string target_m7, string target_m8, string target_m9, string target_m10, string target_m11, string target_m12
            , string mtef_1, string mtef_2, string mtef_3, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(29);
                dbManager.AddParameters(0, "@bgm_strat_kpi_name", bgm_strat_kpi_name);
                dbManager.AddParameters(1, "@bgm_strat_kpi_descr", bgm_strat_kpi_descr);
                dbManager.AddParameters(2, "@unit", unit);
                dbManager.AddParameters(3, "@bgm_strat_kpi_weight", bgm_strat_kpi_weight);

                dbManager.AddParameters(4, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(5, "@score_1", string.IsNullOrEmpty(score_1) ? "0" : score_1);
                dbManager.AddParameters(6, "@score_2", string.IsNullOrEmpty(score_2) ? "0" : score_2);
                dbManager.AddParameters(7, "@score_3", string.IsNullOrEmpty(score_3) ? "0" : score_3);
                dbManager.AddParameters(8, "@score_4", string.IsNullOrEmpty(score_4) ? "0" : score_4);
                dbManager.AddParameters(9, "@score_5", string.IsNullOrEmpty(score_5) ? "0" : score_5);

                dbManager.AddParameters(10, "@target_type", target_type);
                dbManager.AddParameters(11, "@target_m1", string.IsNullOrEmpty(target_m1) ? "0" : target_m1);
                dbManager.AddParameters(12, "@target_m2", string.IsNullOrEmpty(target_m2) ? "0" : target_m2);
                dbManager.AddParameters(13, "@target_m3", string.IsNullOrEmpty(target_m3) ? "0" : target_m3);
                dbManager.AddParameters(14, "@target_m4", string.IsNullOrEmpty(target_m4) ? "0" : target_m4);
                dbManager.AddParameters(15, "@target_m5", string.IsNullOrEmpty(target_m5) ? "0" : target_m5);
                dbManager.AddParameters(16, "@target_m6", string.IsNullOrEmpty(target_m6) ? "0" : target_m6);
                dbManager.AddParameters(17, "@target_m7", string.IsNullOrEmpty(target_m7) ? "0" : target_m7);
                dbManager.AddParameters(18, "@target_m8", string.IsNullOrEmpty(target_m8) ? "0" : target_m8);
                dbManager.AddParameters(19, "@target_m9", string.IsNullOrEmpty(target_m9) ? "0" : target_m9);
                dbManager.AddParameters(20, "@target_m10", string.IsNullOrEmpty(target_m10) ? "0" : target_m10);
                dbManager.AddParameters(21, "@target_m11", string.IsNullOrEmpty(target_m11) ? "0" : target_m11);
                dbManager.AddParameters(22, "@target_m12", string.IsNullOrEmpty(target_m12) ? "0" : target_m12);
                dbManager.AddParameters(23, "@mtef_1", string.IsNullOrEmpty(mtef_1) ? "0" : mtef_1);
                dbManager.AddParameters(24, "@mtef_2", string.IsNullOrEmpty(mtef_2) ? "0" : mtef_2);
                dbManager.AddParameters(25, "@mtef_3", string.IsNullOrEmpty(mtef_3) ? "0" : mtef_3);

                dbManager.AddParameters(26, "@updated_by", UpdateBy);
                dbManager.AddParameters(27, "@updated_when", DateTime.Now);
                dbManager.AddParameters(28, "@sequence", Sequence);


                string strsql = "UPDATE bgm_strategy_kpi SET "
                                +" bgm_strat_kpi_name=@bgm_strat_kpi_name,"
                                + " bgm_strat_kpi_descr=@bgm_strat_kpi_descr,"
                                + " unit=@unit,"
                                + " bgm_strat_kpi_weight=@bgm_strat_kpi_weight,"
                                + " bgm_kpi_type_seq=@bgm_kpi_type_seq,"
                                + " score_1=@score_1,"
                                + " score_2=@score_2,"
                                + " score_3=@score_3,"
                                + " score_4=@score_4,"
                                + " score_5=@score_5,"
                                + " target_type=@target_type,"
                                + " target_m1=@target_m1,"
                                + " target_m2=@target_m2,"
                                + " target_m3=@target_m3,"
                                + " target_m4=@target_m4,"
                                + " target_m5=@target_m5,"
                                + " target_m6=@target_m6,"
                                + " target_m7=@target_m7,"
                                + " target_m8=@target_m8,"
                                + " target_m9=@target_m9,"
                                + " target_m10=@target_m10,"
                                + " target_m11=@target_m11,"
                                + " target_m12=@target_m12,"
                                + " mtef_1=@mtef_1,"
                                + " mtef_2=@mtef_2,"
                                + " mtef_3=@mtef_3,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_strat_kpi_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void DeleteStrategyKPI(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_strategy_kpi WHERE bgm_strat_kpi_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
